#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

sql="
with
tmp1_table as 重点帮扶人类分群 人群分类数据不支持
(
select
	num,
	null percent1,
	content,
	'重点帮扶人类分群' name,
	cast(current_date() as string) as operate_time
from (
	select
		content,
		count(name) as num
	from (
		select
			name,
			case when cast(education as int) > 3 then '高校毕业生'
				when is_allowance_family ='1' then '残疾人士'
				else '其他' end as content
		from cy_database.dws_person_unemploy_detail

		)a
	group by content
	)aa
),



tmp2_table1 as 数据没有长垣市的，先拿广州的代替
(
select
	sum(num1) sum1,
	sum(num2) sum2,
	sum(num3) sum3,
	sum(num4) sum4,
	count(1) amount
from (
	select
		case when startup_code is null then 1 end as num1,
		case when startup_code is not null then 1 end as num2,
		case when census_register = '440100' then 1 end as num3,
		case when census_register != '440100' then 1 end as num4
	from cy_database.dws_person_detail
	)a
),

tmp2_table2 as
(
select
	sum1 as num,
	cast((sum1/amount) as decimal(10,4)) as percent1,
	'失业人数' content,
	'就业信息' name,
	cast(current_date() as string) as operate_time
from tmp2_table1
union all
select
	sum1 as num,
	cast((sum2/amount) as decimal(10,4)) as percent1,
	'就业人数' content,
	'就业信息' name,
	cast(current_date() as string) as operate_time
from tmp2_table1
union all
select
	sum1 as num,
	cast((sum3/amount) as decimal(10,4)) as percent1,
	'本地劳动力' content,
	'就业信息' name,
	cast(current_date() as string) as operate_time
from tmp2_table1
union all
select
	sum1 as num,
	cast((sum4/amount) as decimal(10,4)) as percent1,
	'外来务工' content,
	'就业信息' name,
	cast(current_date() as string) as operate_time
from tmp2_table1
),



tmp3_table1 as
(
select
	max(cast(hold_date as date)) m_time
from cy_database.dws_job_fair
),

tmp3_table2 as
(
select
	t1.m_time,
	jf.admission_sum as num1,
	jf.position_sum as num2,
	jf.reach_work_sum as num3
from tmp3_table1 t1
left join cy_database.dws_job_fair jf on t1.m_time = cast(jf.hold_date as date)
),

tmp3_table3 as
(
select
	num1 as num,
	null percent1,
	'求职者总数' content,
	'长垣市就业困难精准帮扶' name,
	cast(m_time as string) operate_time
from tmp3_table2
union all
select
	num2 as num,
	null percent1,
	'提供岗位数' content,
	'长垣市就业困难精准帮扶' name,
	cast(m_time as string) operate_time
from tmp3_table2
union all
select
	num3 as num,
	null percent1,
	'帮扶总人数' content,
	'长垣市就业困难精准帮扶' name,
	cast(m_time as string) operate_time
from tmp3_table2

),




tmp4_table as
(
select
	count(1) as num,
	null percent1,
	'劳动力总数' content,
	'精准对接跟踪' name,
	cast(current_date() as string) as operate_time
from cy_database.dws_person_detail

union all
select
	count(1) as num,
	null percent1,
	'就业困难用户数量' content,
	'精准对接跟踪' name,
	cast(current_date() as string) as operate_time
from cy_database.dws_person_unemploy_detail

union all
select
	100 num,
	null percent1,
	'服务跟进人数' content,
	'精准对接跟踪' name,
	cast(current_date() as string) as operate_time

union all
select
	sum(reach_work_sum) as num,
	null percent1,
	'意向达成人数' content,
	'精准对接跟踪' name,
	cast(current_date() as string) as operate_time
from cy_database.dws_job_fair

union all
select
	sum(sign_ldht_sum) as num,
	null percent1,
	'成功入职' content,
	'精准对接跟踪' name,
	cast(current_date() as string) as operate_time
from cy_database.dws_job_fair
),


tmp5_table1 as
(
select
	df_time,
	sum(num1) sum1,
	sum(num2) sum2
from (
	select
		date_format(hold_date,'yyyy-MM') as df_time,
		admission_sum  as num1,
		reach_work_sum as num2
	from cy_database.dws_job_fair
	)a
group by df_time
order by df_time desc
limit 6
),

tmp5_table2 as
(
select
	sum1 as num,
	null percent1,
	'目标帮扶人数' content,
	'就业帮扶人数' name,
	df_time as operate_time
from tmp5_table1

union all
select
	sum2 as num,
	null percent1,
	'已帮扶人数' content,
	'就业帮扶人数' name,
	df_time as operate_time
from tmp5_table1
),


tmp6_table1 as
(
select
	name,
	teach_date,
	is_train,
	train_date,
	count(1) over() as amount1,
	count(1) over(partition by is_train) amount2
from (
	select
		name,
		date_format(teach_date,'yyyy-MM') teach_date,
		is_train,
		date_format(train_date,'yyyy-MM') train_date
	from cy_database.ods_help_people
	)a
),

tmp6_table2 as
(
(select
	amount1 as num,
	null percent1,
	'就业指导' content,
	'就业指导/培训' name,
	cast(current_date() as string) as operate_time
from tmp6_table1
LIMIT 1)

union all
(select
	amount2 as num,
	null percent1,
	'就业培训' content,
	'就业指导/培训' name,
	cast(current_date() as string) as operate_time
from tmp6_table1
where is_train ='1'
LIMIT 1)

union all
select
	count(1) as num,
	null percent1,
	'培训人数/月' content,
	'就业指导/培训' name,
	train_date as operate_time
from tmp6_table1
where is_train ='1'
group by train_date

union all
select
	count(1) as num,
	null percent1,
	'指导人数/月' content,
	'就业指导/培训' name,
	teach_date as operate_time
from tmp6_table1
where is_train !='1'
group by teach_date
)

insert overwrite table cy_database.ads_big_screen_result_help
select num,percent1,content,name,operate_time from tmp1_table

union all
select num,percent1,content,name,operate_time from tmp2_table2

union all
select num,percent1,content,name,operate_time from tmp3_table3

union all
select num,percent1,content,name,operate_time from tmp4_table

union all
select num,percent1,content,name,operate_time from tmp5_table2

union all
select num,percent1,content,name,operate_time from tmp6_table2

;

"

$hive -e "$sql"